package com.xuxueli.applyModules.utils;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 */
public class CompareIndex {

    private static String PG_EXCEL_2007 = "D:\\downfiles\\对比表结构索引\\opduibi\\pg_index_stk.xls";
    private static String ORACLE_EXCEL_2007 = "D:\\downfiles\\对比表结构索引\\oralce_colums\\index_stk.xls";
    private static String ORACLE_INDEXTYPE = "D:\\downfiles\\对比表结构索引\\stk_index_type.xlsx";

    /**
     * 读取Excel2007的示例方法 （单个sheet）
     *
     * @param filePath
     *            文件路径
     * @return 返回获得的实体
     */
    public static Map<String, IndexEntity> oracleReadFromXLSX2007(String filePath) {
        // Excel文件对象
        File excelFile;
        // 输入流对象
        InputStream is = null;
        // 返回封装数据的List
        List<IndexEntity> studentList = new ArrayList<>();

        Map<String, IndexEntity> indexSet = new HashMap<>(16);
        // 每一个雇员信息对象
        try {
            excelFile = new File(filePath);
            // 获取文件输入流
            is = new FileInputStream(excelFile);
            // XSSFWorkbook workbook2007 = new XSSFWorkbook(is);// 创建Excel2007文件对象
            org.apache.poi.ss.usermodel.Workbook workbook2007 = WorkbookFactory.create(is);
            // XSSFSheet sheet = workbook2007.getSheetAt(0);// 取出第一个工作表，索引是0
            org.apache.poi.ss.usermodel.Sheet sheet = workbook2007.getSheetAt(0);
            // 开始循环遍历行，表头不处理，从1开始
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                // 实例化Student对象
                IndexEntity indexEntity = new IndexEntity();
                // HSSFRow row = sheet.getRow(i);// 获取行对象
                // 获取行对象
                Row row = sheet.getRow(i);
                // 如果为空，不处理
                if (row == null) {
                    continue;
                }
                // 循环遍历单元格
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    // 获取单元格对象
                    Cell cell1 = row.getCell(0);
                    indexEntity.setTableName(cell1.getStringCellValue().toLowerCase());
                    Cell cell2 = row.getCell(1);
                    indexEntity.setIndexName(cell2.getStringCellValue().toLowerCase());
                    Cell cell3 = row.getCell(2);
                    String indexColumns = cell3.getStringCellValue().toLowerCase().replace(" ", "").trim();
                    indexEntity
                        .setIndexColumns(Stream.of(indexColumns.split(",")).sorted().collect(Collectors.toList()));
                }
                // 数据装入List
                studentList.add(indexEntity);
                indexSet.put(indexEntity.getIndexName(), indexEntity);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 关闭文件流
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return indexSet;
    }

    /**
     * 获取type
     */
    public static Map<String, String> getType(String filePath) {
        // Excel文件对象
        File excelFile;
        // 输入流对象
        InputStream is = null;
        // 返回封装数据的List
        List<IndexEntity> studentList = new ArrayList<>();

        Map<String, String> indexSet = new HashMap<>(16);
        // 每一个雇员信息对象
        try {
            excelFile = new File(filePath);
            // 获取文件输入流
            is = new FileInputStream(excelFile);
            // XSSFWorkbook workbook2007 = new XSSFWorkbook(is);// 创建Excel2007文件对象
            org.apache.poi.ss.usermodel.Workbook workbook2007 = WorkbookFactory.create(is);
            // XSSFSheet sheet = workbook2007.getSheetAt(0);// 取出第一个工作表，索引是0
            org.apache.poi.ss.usermodel.Sheet sheet = workbook2007.getSheetAt(0);
            // 开始循环遍历行，表头不处理，从1开始
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                // 实例化Student对象
                IndexEntity indexEntity = new IndexEntity();
                // HSSFRow row = sheet.getRow(i);// 获取行对象
                // 获取行对象
                Row row = sheet.getRow(i);
                // 如果为空，不处理
                if (row == null) {
                    continue;
                }
                // 循环遍历单元格
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    // 获取单元格对象
                    Cell cell1 = row.getCell(0);
                    indexEntity.setTableName(cell1.getStringCellValue().toLowerCase());
                    Cell cell2 = row.getCell(1);
                    indexEntity.setIndexName(cell2.getStringCellValue().toLowerCase());
                    indexSet.put(cell1.getStringCellValue().toLowerCase(), cell2.getStringCellValue().toLowerCase());
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 关闭文件流
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return indexSet;
    }

    public static void main(String[] args) {
        Map<String, IndexEntity> oracleIndexs = oracleReadFromXLSX2007(ORACLE_EXCEL_2007);
        Map<String, IndexEntity> pgIndexs = oracleReadFromXLSX2007(PG_EXCEL_2007);
        Map<String, String> oracleType = getType(ORACLE_INDEXTYPE);
        System.out.println("数据库类型\t表名\t类型\t索引名称\t栏位");
        for (String key : oracleIndexs.keySet()) {
            IndexEntity oracleIndexEntity = oracleIndexs.get(key);
            if (null != oracleIndexEntity) {
                IndexEntity pgIndexEntity = pgIndexs.get(key);
                if (null != pgIndexEntity) {
                    List<String> oracleIndexColumns = oracleIndexEntity.getIndexColumns();
                    List<String> pgIndexColumns = pgIndexEntity.getIndexColumns();
                    if (pgIndexColumns.containsAll(oracleIndexColumns)
                        && oracleIndexColumns.containsAll(pgIndexColumns)) {
                    } else {
                        if (pgIndexColumns.size() > oracleIndexColumns.size()) {
                            System.out.println(
                                "ORACLE\t" + oracleIndexEntity.getTableName() + "\t" + oracleType.get(key) + "\t"
                                    + oracleIndexEntity.getIndexName() + "\t" + oracleIndexEntity.getIndexColumns());
                            System.out.println("PG\t" + pgIndexEntity.getTableName() + "\t" + oracleType.get(key) + "\t"
                                + pgIndexEntity.getIndexName() + "\t" + pgIndexEntity.getIndexColumns());
                        } else {
                            System.out.println(
                                "ORACLE\t" + oracleIndexEntity.getTableName() + "\t" + oracleType.get(key) + "\t"
                                    + oracleIndexEntity.getIndexName() + "\t" + oracleIndexEntity.getIndexColumns());
                            System.out.println("PG\t" + pgIndexEntity.getTableName() + "\t" + oracleType.get(key) + "\t"
                                + pgIndexEntity.getIndexName() + "\t" + pgIndexEntity.getIndexColumns());
                        }

                    }
                } else {
                    // System.out.println("key is null:"+key);
                }
            }

        }
    }
}
